<!DOCTYPE HTML>
<html lang="zh-CN">
<head>
    

<head><meta name="generator" content="Hexo 3.8.0">
    <meta charset="utf-8">
    <meta name="keywords" content="orcale总结, Zhou•Yong">
    <meta name="description" content="orcale 导出和导入数据有些网友报错，一般都是没有dba权限，授权：
sqlplus / as sysdba
进入后直接创建用户并授予dba的权限
例如：
sqlplus / as sysdba
SQL*Plus: Release 11">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="renderer" content="webkit|ie-stand|ie-comp">
    <meta name="mobile-web-app-capable" content="yes">
    <meta name="format-detection" content="telephone=no">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
    <title>orcale总结 | Zhou•Yong</title>
    <link rel="icon" type="image/png" href="/favicon.png">

    <link rel="stylesheet" type="text/css" href="/libs/awesome/css/font-awesome.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/materialize/css/materialize.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/aos/aos.css">
    <link rel="stylesheet" type="text/css" href="/libs/animate/animate.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/lightGallery/css/lightgallery.min.css">
    <link rel="stylesheet" type="text/css" href="/css/matery.css">
    <link rel="stylesheet" type="text/css" href="/css/my.css">

    <script src="/libs/jquery/jquery-2.2.0.min.js"></script>
<link rel="stylesheet" href="/css/prism-tomorrow.css" type="text/css"></head>

</head>

<body>

<header class="navbar-fixed">
    <nav id="headNav" class="bg-color nav-transparent">
        <div id="navContainer" class="container">
            <div class="nav-wrapper">
                <div class="brand-logo">
                    <a href="/" class="waves-effect waves-light">
                        
                        <img src="/medias/logo.png" class="logo-img hide-on-small-only">
                        
                        <span class="logo-span">Zhou•Yong</span>
                    </a>
                </div>
                <a href="#" data-activates="mobile-nav" class="button-collapse"><i class="fa fa-navicon"></i></a>
<ul class="right">
    
    <li class="hide-on-med-and-down">
        <a href="/" class="waves-effect waves-light">
            
            <i class="fa fa-home"></i>
            
            <span>主页</span>
        </a>
    </li>
    
    <li class="hide-on-med-and-down">
        <a href="/about" class="waves-effect waves-light">
            
            <i class="fa fa-user-circle-o"></i>
            
            <span>个人介绍</span>
        </a>
    </li>
    
    <li class="hide-on-med-and-down">
        <a href="/categories" class="waves-effect waves-light">
            
            <i class="fa fa-bookmark"></i>
            
            <span>分类</span>
        </a>
    </li>
    
    <li class="hide-on-med-and-down">
        <a href="/tags" class="waves-effect waves-light">
            
            <i class="fa fa-tags"></i>
            
            <span>标签</span>
        </a>
    </li>
    
    <li class="hide-on-med-and-down">
        <a href="/friends" class="waves-effect waves-light">
            
            <i class="fa fa-address-book"></i>
            
            <span>推荐</span>
        </a>
    </li>
    
    <li>
        <a id="toggleSearch" class="waves-effect waves-light">
            <i id="searchIcon" class="mdi-action-search"></i>
        </a>
    </li>

</ul>

<div class="side-nav" id="mobile-nav">

    <div class="mobile-head bg-color">
        
        <img src="/medias/logo.png" class="logo-img circle responsive-img">
        
        <div class="logo-name">Zhou•Yong</div>
        <div class="logo-desc">
            
            你迈出的步伐不非得很大，只要你是在正确的方向前进就行
            
        </div>
    </div>

    <ul class="menu-list mobile-menu-list">
        
        <li>
            <a href="/" class="waves-effect waves-light">
                
                <i class="fa fa-fw fa-home"></i>
                
                主页
            </a>
        </li>
        
        <li>
            <a href="/about" class="waves-effect waves-light">
                
                <i class="fa fa-fw fa-user-circle-o"></i>
                
                个人介绍
            </a>
        </li>
        
        <li>
            <a href="/categories" class="waves-effect waves-light">
                
                <i class="fa fa-fw fa-bookmark"></i>
                
                分类
            </a>
        </li>
        
        <li>
            <a href="/tags" class="waves-effect waves-light">
                
                <i class="fa fa-fw fa-tags"></i>
                
                标签
            </a>
        </li>
        
        <li>
            <a href="/friends" class="waves-effect waves-light">
                
                <i class="fa fa-fw fa-address-book"></i>
                
                推荐
            </a>
        </li>
        
        
    </ul>

    <div class="social-link"><a href="#!" class="tooltipped" data-tooltip="邮件联系我: zy20081@126.com" data-position="top" data-delay="50">
    <i class="fa fa-envelope-open"></i>
</a>
<a href="#!" class="tooltipped" data-tooltip="QQ联系我: 314153271" data-position="top" data-delay="50">
    <i class="fa fa-qq"></i>
</a>

<a href="/atom.xml" class="tooltipped" target="_blank" data-tooltip="RSS 订阅" data-position="top" data-delay="50">
    <i class="fa fa-rss"></i>
</a>
</div>
</div>

            </div>
        </div>

        
    </nav>
</header>





<div class="bg-cover post-cover" style="background-image: url('/medias/featureimages/18.jpg')">
    <div class="container">
        <div class="row">
            <div class="col s12 m12 l12">
                <div class="brand">
                    <div class="description center-align post-title">
                        orcale总结
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>



<main class="post-container content">

    
    <link rel="stylesheet" href="/libs/tocbot/tocbot.css">
<style>
    #articleContent h1,
    #articleContent h2,
    #articleContent h3,
    #articleContent h4,
    #articleContent h5,
    #articleContent h6 {
        padding-top: 76px;
        margin-top: -76px;
    }

    #articleContent h1 {
        line-height: 3.5rem;
    }

    #articleContent h2 {
        line-height: 3.2rem;
    }

    #articleContent h3 {
        line-height: 2.8rem;
    }

    #articleContent h4 {
        line-height: 2.5rem;
    }

    #articleContent h5 {
        line-height: 2.2rem;
    }

    #articleContent h6 {
        line-height: 1.9rem;
    }

    #articleContent :focus {
        outline: none;
    }

    .toc-fixed {
        position: fixed;
        top: 64px;
    }

    .toc-widget {
        padding-left: 20px;
    }

    .toc-widget .toc-title {
        margin: 35px 0 15px 0;
        padding-left: 17px;
        font-size: 1.5rem;
        font-weight: bold;
        line-height: 1.5rem;
    }

    .toc-widget ol {
        padding: 0;
        list-style: none;
    }

    #toc-content ol {
        padding-left: 10px;
    }

    #toc-content ol li {
        padding-left: 10px;
    }

    #toc-content .toc-link:hover {
        color: #42b983;
        font-weight: 700;
        text-decoration: underline;
    }

    #toc-content .toc-link::before {
        background-color: transparent;
        max-height: 25px;
    }

    #toc-content .is-active-link {
        color: #42b983;
    }

    #toc-content .is-active-link::before {
        background-color: #42b983;
    }
</style>
<div class="row">
    <div class="col s12 m12 l9">
        <!-- 文章内容详情 -->
<div id="artDetail">
    <div class="card">
        <div class="card-content article-info">
            
            <div class="article-tag">
                
                <a href="/tags/orcale/" target="_blank">
                    <span class="chip bg-color">orcale</span>
                </a>
                
            </div>
            
            <div class="post-info">
                
                <span class="post-cate">
                    <i class="fa fa-bookmark fa-fw icon-category"></i>
                    
                    <a href="/categories/数据库/" class="post-category" target="_blank">
                        数据库
                    </a>
                    
                </span>
                

                <span class="post-date">
                    <i class="fa fa-clock-o fa-fw"></i>2019-01-02
                </span>
            </div>
        </div>
        <hr>
        <div class="card-content article-card-content">
            <div id="articleContent">
                <h2 id="orcale-导出和导入数据"><a href="#orcale-导出和导入数据" class="headerlink" title="orcale 导出和导入数据"></a>orcale 导出和导入数据</h2><p>有些网友报错，一般都是没有dba权限，授权：</p>
<pre class=" language-bash"><code class="language-bash">sqlplus / as sysdba
进入后直接创建用户并授予dba的权限
例如：
sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on 星期二 3月 22 09:10:48 2011
Copyright <span class="token punctuation">(</span>c<span class="token punctuation">)</span> 1982, 2008, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL<span class="token operator">></span> create user testmin identified by testmin<span class="token punctuation">;</span>
用户已创建。
SQL<span class="token operator">></span> grant dba to testmin<span class="token punctuation">;</span>
授权成功。
SQL<span class="token operator">></span>
</code></pre>
<pre class=" language-bash"><code class="language-bash">数据库导出功能：
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file<span class="token operator">=</span>d:\daochu.dmp full<span class="token operator">=</span>y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file<span class="token operator">=</span>d:\daochu.dmp owner<span class="token operator">=</span><span class="token punctuation">(</span>system,sys<span class="token punctuation">)</span>
3 将数据库中的表table1 、table2导出
exp system/manager@TEST file<span class="token operator">=</span>d:\daochu.dmp tables<span class="token operator">=</span><span class="token punctuation">(</span>table1,table2<span class="token punctuation">)</span>
4 将数据库中的表table1中的字段filed1以<span class="token string">"00"</span>打头的数据导出
exp system/manager@TEST file<span class="token operator">=</span>d:\daochu.dmp tables<span class="token operator">=</span><span class="token punctuation">(</span>table1<span class="token punctuation">)</span> query<span class="token operator">=</span>\" where filed1 like <span class="token string">'00%'</span>\"
</code></pre>
<p>数据的导入</p>
<pre class=" language-bash"><code class="language-bash">1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file<span class="token operator">=</span>d:\daochu.dmp ignore<span class="token operator">=</span>y
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上 ignore<span class="token operator">=</span>y 就可以了。
2 将d:\daochu.dmp中的表table1 导入
imp system/manager@TEST file<span class="token operator">=</span>d:\daochu.dmp tables<span class="token operator">=</span><span class="token punctuation">(</span>table1<span class="token punctuation">)</span>
</code></pre>
<h2 id="orcale设置权限"><a href="#orcale设置权限" class="headerlink" title="orcale设置权限"></a>orcale设置权限</h2><p>Oracle 创建普通用户，并赋予权限</p>
<pre class=" language-bash"><code class="language-bash">1.采用sys or system / manager as sysdba<span class="token punctuation">;</span> 连接数据库。
2.创建普通用户konglin: create user konglin identified by pwd_oracle<span class="token punctuation">;</span>
删除用户, drop user konglin<span class="token punctuation">;</span>
3.授予用户登录数据库的权限： grant create session to konglin<span class="token punctuation">;</span>
4.授予用户操作表空间的权限：<span class="token operator">&lt;</span>br<span class="token operator">></span>
grant unlimited tablespace to konglin<span class="token punctuation">;</span><span class="token operator">&lt;</span>br<span class="token operator">></span>
grant create tablespace to konglin<span class="token punctuation">;</span><span class="token operator">&lt;</span>br<span class="token operator">></span>
grant alter tablespace to konglin<span class="token punctuation">;</span><span class="token operator">&lt;</span>br<span class="token operator">></span>
grant drop tablespace to konglin<span class="token punctuation">;</span><span class="token operator">&lt;</span>br<span class="token operator">></span>
grant manage tablespace to konglin<span class="token punctuation">;</span>
5.授予用户操作表的权限：
grant create table to konglin<span class="token punctuation">;</span> <span class="token punctuation">(</span>包含有create index权限, alter table, drop table权限<span class="token punctuation">)</span>
6.授予用户操作视图的权限:
grant create view to konglin<span class="token punctuation">;</span> <span class="token punctuation">(</span>包含有alter view, drop view权限<span class="token punctuation">)</span>
7.授予用户操作触发器的权限：
grant create trigger to konglin<span class="token punctuation">;</span> <span class="token punctuation">(</span>包含有alter trigger, drop trigger权限<span class="token punctuation">)</span>
8.授予用户操作存储过程的权限：
grant create procedure to konglin<span class="token punctuation">;</span><span class="token punctuation">(</span>包含有alter procedure, drop procedure 和function 以及 package权限<span class="token punctuation">)</span>
9.授予用户操作序列的权限：
grant create sequence to konglin<span class="token punctuation">;</span> <span class="token punctuation">(</span>包含有创建、修改、删除以及选择序列<span class="token punctuation">)</span>
10.授予用户回退段权限：
grant create rollback segment to konglin<span class="token punctuation">;</span>
grant alter rollback segment to konglin<span class="token punctuation">;</span>
grant drop rollback segment to konglin<span class="token punctuation">;</span>
11.授予用户同义词权限：
grant create synonym to konglin<span class="token punctuation">;</span><span class="token punctuation">(</span>包含drop synonym权限<span class="token punctuation">)</span>
grant create public synonym to konglin<span class="token punctuation">;</span>
grant drop public synonym to konglin<span class="token punctuation">;</span>
12.授予用户关于用户的权限：
grant create user to konglin<span class="token punctuation">;</span>
grant alter user to konglin<span class="token punctuation">;</span>
grant become user to konglin<span class="token punctuation">;</span>
grant drop user to konglin<span class="token punctuation">;</span>
13.授予用户关于角色的权限：
grant create role to konglin<span class="token punctuation">;</span>
14.授予用户操作概要文件的权限
grant create profile to konglin<span class="token punctuation">;</span>
grant alter profile to konglin<span class="token punctuation">;</span>
grant drop profile to konglin<span class="token punctuation">;</span>
15.允许从sys用户所拥有的数据字典表中进行选择
grant <span class="token keyword">select</span> any dictionary to konglin<span class="token punctuation">;</span>
</code></pre>
<h2 id="sqlplus创建表基本操作"><a href="#sqlplus创建表基本操作" class="headerlink" title="sqlplus创建表基本操作"></a>sqlplus创建表基本操作</h2><pre class=" language-bash"><code class="language-bash">最高用户（系统管理员）可以删别人用户 drop user
SQL<span class="token operator">></span> create user xiaoming identified by 123<span class="token punctuation">;</span> 创建用户
SQL<span class="token operator">></span> grant connect to xiaoming<span class="token punctuation">;</span>授权
SQL<span class="token operator">></span> grant <span class="token keyword">select</span> on emp to xiaoming；授权给小明
SQL<span class="token operator">></span>show user 查看当前用户
SQL<span class="token operator">></span>conn sysstem/manager 连接用户 账户/密码 连接
SQL<span class="token operator">></span>对表user没权限 alter user 数据库用户名 <span class="token function">quota</span> unlimited on USERS<span class="token punctuation">;</span>
SQL<span class="token operator">></span>Grant
Create session,create table,select any table,update any table,insert any
table,delete any table To user_name
alter user zy20081 <span class="token function">quota</span> unlimited on USERS  空间操作权限 导入表
给所有权限
disc<span class="token punctuation">[</span>onnect<span class="token punctuation">]</span>断开连接
passw<span class="token punctuation">[</span>ord<span class="token punctuation">]</span> 用户名 修改密码
sys/system可以修改别人密码
<span class="token keyword">exit</span> 退出
文件操作命令
sql<span class="token operator">></span>START d:\a.sql 运行一个脚本
SQL<span class="token operator">></span>edit d:\a.sql 编辑
。。。。。。截取中间内容。。。。。。。。。
SQL<span class="token operator">></span> spool d:\bb.sql 建立
SQL<span class="token operator">></span> <span class="token keyword">select</span> * from 表
SQL<span class="token operator">></span> spool off<span class="token punctuation">;</span> 保存 
。。。。。。。。。。。。。
SQL<span class="token operator">></span> show linesize 设置显示行的宽度 默认是80个字符
SQL<span class="token operator">></span>set linesize 90
SQL<span class="token operator">></span> <span class="token keyword">set</span> pagesize 2<span class="token punctuation">;</span>设置分页
。。。。。。。。。。。。。。。。。。。。。
SQL<span class="token operator">></span> create user xiaoming identified by m123 创建账户和密码
密码以字母开头 
SQL<span class="token operator">></span>drop user xiaoming 删除用户
</code></pre>
<h2 id="日志基本管理"><a href="#日志基本管理" class="headerlink" title="日志基本管理"></a>日志基本管理</h2><pre class=" language-bash"><code class="language-bash">1.强制日志切换（forcing log switches）
alter system switch logfile<span class="token punctuation">;</span>
2.强制执行检查点（forcing checkpoints）
alter system checkpoint<span class="token punctuation">;</span>
3.增加一个重做日志组 （adding online redo log groups）
alter fatabases add logfile <span class="token punctuation">[</span> group 4 <span class="token punctuation">]</span> <span class="token punctuation">(</span><span class="token string">'/disk3/log4a.rdo'</span>,<span class="token string">'/disk4/log4b.rdo'</span><span class="token punctuation">)</span> size 1M<span class="token punctuation">;</span>
4.增加一个重做日志文件<span class="token punctuation">(</span> adding online redo log memebers <span class="token punctuation">)</span>
alter databases add logfile member
<span class="token string">'/disk3/log1b.rdo'</span> to group 1,
<span class="token string">'/disk4/log2b.rdo'</span> to group 2<span class="token punctuation">;</span>
5.改变重做日志文件名 （changes the name of the online redo logfile）
alter databases <span class="token function">rename</span> <span class="token function">file</span> 
<span class="token string">'c:/oracle/oradata/oradb/redo01.log'</span>
to <span class="token string">'c:/oracle/oracdata/redo01.log'</span><span class="token punctuation">;</span>
6.删除重做日志 （drio online redo log groups）
alter database drop logfile groupp 3<span class="token punctuation">;</span>
7.删除重做日志组（drop online redo log members）
alter database drop logfile member <span class="token string">'c:/oracle/readata/red01.log'</span><span class="token punctuation">;</span>
8.清空重做日志（clearing online redo log files）
alter database <span class="token function">clear</span> <span class="token punctuation">[</span>unarchived<span class="token punctuation">]</span> logfile <span class="token string">'c:/oracle/log2a.rdo'</span><span class="token punctuation">;</span>
9.使用logminer分析重做日志文件（using logminer analyzing redo logfiles）
a.  <span class="token keyword">in</span> the init.ora specify utl_file_dir <span class="token operator">=</span> <span class="token string">' '</span>
b.  execute dbms_logmnr_d.build<span class="token punctuation">(</span><span class="token string">'oradb.ora'</span>,<span class="token string">'c:\oracle\oradb\log'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
c.  execute dbms_logmnr_add_logfile<span class="token punctuation">(</span><span class="token string">'c:\oracle\oradata\oradb\redo01.log'</span>,dbms_logmnr.new<span class="token punctuation">)</span><span class="token punctuation">;</span>
d.  execute dbms_logmnr.add_logfile<span class="token punctuation">(</span><span class="token string">'c:\oracle\oradata\oradb\redo02.log'</span>,dbms_logmnr.addfile<span class="token punctuation">)</span><span class="token punctuation">;</span>
e.  execute dbms_logmnr.start_logmnr<span class="token punctuation">(</span>dictfilename<span class="token operator">=</span><span class="token operator">></span><span class="token string">'c:\oracle\oradb\log\oradb.ora'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
f.  <span class="token keyword">select</span> * from <span class="token function">v</span><span class="token variable">$logmnr_contents</span><span class="token punctuation">(</span>v<span class="token variable">$logmnr_dictionary</span>,v<span class="token variable">$logmnr_parameters</span> <span class="token function">v</span><span class="token variable">$logmnr_logs</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
g.  execute dbms_logmnr.end_logmnr<span class="token punctuation">;</span>
</code></pre>
<h2 id="表空间管理"><a href="#表空间管理" class="headerlink" title="表空间管理"></a>表空间管理</h2><pre class=" language-bash"><code class="language-bash">1.创建表空间（create tablespaces）
create tablespace table_name 
datafile <span class="token string">'c:\oracle\oradata\file1.dbf'</span>
size 100M
minimum extent 550k <span class="token punctuation">[</span>logging/nologging<span class="token punctuation">]</span>
default storage <span class="token punctuation">(</span>initial 500k next 500k maxextents 500 pctinccease 0<span class="token punctuation">)</span>
<span class="token punctuation">[</span>online/offline<span class="token punctuation">]</span>
<span class="token punctuation">[</span>permanent/temporary<span class="token punctuation">]</span>
<span class="token punctuation">[</span>extent_management_clause<span class="token punctuation">]</span>

2.创建本地管理的表空间<span class="token punctuation">(</span> locally managed tabalespace <span class="token punctuation">)</span>
create tablespace user_data 
datafile <span class="token string">'c:\oracle\oradata\user_data001.dbf'</span>
size 500M
extent management 
local uniform size 10M<span class="token punctuation">;</span>

3. 创建临时表空间（temporary tablespace）
create temporary tablespace temp 
tempfile <span class="token string">'c:\oracle\oradata\temppp01.dbf'</span>
size 500M
extent management
local uniform size 10M<span class="token punctuation">;</span>

4.改变表空间的存储参数（change the storage setting）
alter tablespace app_data minimum extent 2M<span class="token punctuation">;</span>
or
alter tablespace app_date 
default storage<span class="token punctuation">(</span> initial 2M next 2M maxextents 999<span class="token punctuation">)</span><span class="token punctuation">;</span>

5.使表空间离线或连线（taking tablespace offline or online）
alter tablespace app_data offline<span class="token punctuation">;</span>
or
alter tablespace app_data online<span class="token punctuation">;</span>

6.设置表空间为只读、可写模式（read_only tablespace）
alter tablespace app_data <span class="token function">read</span> only <span class="token operator">|</span> <span class="token function">write</span><span class="token punctuation">;</span>

7.删除表空间（droping tablespace）
drop tablespace app_data including contents<span class="token punctuation">;</span>

8.允许数据文件自动扩张（enableing automatic extension of data files）
alter tablespace app_data add 
datafile <span class="token string">'c:\oracle\oradata\app_data01.dbf'</span>
size 200M
autoextend on next 10M
maxsize 500M<span class="token punctuation">;</span>

9.手动改变数据文件大小（change the size fo data files manually）
alter database datafile <span class="token string">'c:\oracle\oradata\app_data.dbf'</span>
resize 200M<span class="token punctuation">;</span>

10.改变表空间中的数据文件（mocing data files:alter tablespace）
alter tablespace app_data 
<span class="token function">rename</span> datafile <span class="token string">'c:\oracle\oradata\app_data.dbf'</span>
 to <span class="token string">'c:\oracle\app_data.dbf'</span><span class="token punctuation">;</span>

11.修改数据库中的数据文件（moving data files:alter database）
alter database <span class="token function">rename</span> <span class="token function">file</span> <span class="token string">'c:\oracle\oradata\app_data.dbf'</span>
to <span class="token string">'c:\oracle\app_data.dbf'</span><span class="token punctuation">;</span>
</code></pre>
<h2 id="表管理"><a href="#表管理" class="headerlink" title="表管理"></a>表管理</h2><pre class=" language-bash"><code class="language-bash">1.创建表
create table table_name <span class="token punctuation">(</span>
    column datatype,
    <span class="token punctuation">[</span>column datatype<span class="token punctuation">]</span>
    <span class="token punctuation">..</span><span class="token punctuation">..</span>.  
<span class="token punctuation">)</span><span class="token punctuation">;</span>
tablespace tablespace_name 
<span class="token punctuation">[</span> ppctfree integer <span class="token punctuation">]</span> 
<span class="token punctuation">[</span> pctused integer <span class="token punctuation">]</span>
<span class="token punctuation">[</span> initrans integer <span class="token punctuation">]</span>
<span class="token punctuation">[</span> maxtrans integer <span class="token punctuation">]</span>
storage<span class="token punctuation">(</span> initial 200k next 200k ppctincrease 0 maxextents 50<span class="token punctuation">)</span> <span class="token punctuation">[</span> logging <span class="token operator">|</span> nologging <span class="token punctuation">]</span>  <span class="token punctuation">[</span> cache <span class="token operator">|</span> nocaahe <span class="token punctuation">]</span>

2.复制一个已存在的表（copy an existing table）
create table table_name 
<span class="token punctuation">[</span> logging <span class="token operator">|</span> nologging <span class="token punctuation">]</span> 
as subquery

3.创建一个临时表（create temporary table）
create global temporary table xay_temp as <span class="token keyword">select</span> * from xay<span class="token punctuation">;</span>
on commint preserve rows/on commit delete rows

4.pctfree 和 pctused 参数计算公式
pctfree <span class="token operator">=</span> <span class="token punctuation">(</span>average row size - initial row size<span class="token punctuation">)</span>*100/average row size
pctused <span class="token operator">=</span> 100-pctfree-<span class="token punctuation">(</span>average row size * 100/available data space<span class="token punctuation">)</span>

    pctfree <span class="token keyword">:</span> 指定表内每个数据块中空间的百分比。pctfree的值必须介于0和99之间。如果为零，表示可以通过插入新行来填充整个块。
　　　　　　　　缺省值为10.此值表示每个块中保留着10%的空间，用于更新现有的行以及插入新行，每个块最多可以填充到90%

    pctused <span class="token keyword">:</span> 指定为表内每个数据块的已用空间的最小百分比。如果一个块的已用空间低于pctused，则可在该块中插入行。pctused的值介于0和99之间的整数，缺省值为40.

结合pctfree 和 pctused 就可以确定将新行插入到现有数据块中，还是插入到新块中。这两个参数值的和必须小于或等于100.使用这两个参数可以更有效地利用表内的空间。

设置 pctfree 和pctused
    pctfree 值越高，可为数据库块内的更新提供的空间就越大。如果表存在下面两种情况，则应设置一个更高的值：？某些列最初为null，后来更新为某个值？ 
　　　　　　某些列由于更新，大小可能增加pctfree的值越高，块密度就越低，即每个块容纳的行数就越少。上面的公式确保块中有足够的空间供行增长使用。
    pctused 以确保只有在具备足够空间来容纳一个平均大小的行时才将块返回到空闲列表中。
　　　　　　如果空闲列表中的某个块没有足够的空间来插入一行，oracle服务器将查找空闲列表中的下个块、直到找到具备足够空间的块或者到达列表的末尾，这种线性扫描才会结束。
　　　　　　使用给定的公式可以增加找到具有所有需空闲空间的块的概率，从而缩短扫描空闲列表时间。

注：可以使用 analyze table 命令估算平均行大小的值。
注：oracle9i “自动段空间管理” 功能可替代 pctused、freelists 和 freelist       groups.

5.改变存储和块利用率参数（change storage and block utilization parameter）
alter table table_name pctfree<span class="token operator">=</span>30 pctused<span class="token operator">=</span>50 storage<span class="token punctuation">(</span>next 500k minextents 2 maxextents 100<span class="token punctuation">)</span><span class="token punctuation">;</span>

6.手工分配区间（extents）<span class="token punctuation">(</span>manually allxoating extents<span class="token punctuation">)</span>
alter table table_name allocate extent <span class="token punctuation">(</span>size 500k datafile <span class="token string">'c:/oracle/data.dbf'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

7.改变表的所属表空间（move tablespace）
alter table employee move tablespace <span class="token function">users</span><span class="token punctuation">;</span>

8.释放表中未用空间（deallocate of unused space）
alter table table_name deallocate unused <span class="token punctuation">[</span> keep integer <span class="token punctuation">]</span>

9.截断表（truncate）（truncate a table ）
truncate table table_name<span class="token punctuation">;</span>

截断一个表将删除表中所有行，从而释放已使用的空间。
对应的索引将被截断
注：truncate table 不是DML语句，是DDL语句。另外truncate与delete的区别是吗，delete不释放空间，truncate释放空间。

10.删除表（drop a table）
drop table table_name <span class="token punctuation">[</span>cascade constraints<span class="token punctuation">]</span><span class="token punctuation">;</span>

11.删除列（drop a cloumn）
alter table table_name drop column commentd cascade constraints checkppoint 1000<span class="token punctuation">;</span>
or
alter table table_name drop columns <span class="token keyword">continue</span><span class="token punctuation">;</span>

12.表示某一列为未使用（unused）（mark a column sa unused）
alter table table_name <span class="token keyword">set</span> unused column comments xascade constraints<span class="token punctuation">;</span>
or
alter table table_name dropp unused columns checkpoint 1000<span class="token punctuation">;</span>
or
alter table orders drop columns <span class="token keyword">continue</span> checkpoint 1000 data_dictionary <span class="token keyword">:</span> dba_unused_col_tabs<span class="token punctuation">;</span>

除将列从表中删除以外，还可以先将列标记为 “未使用”，以后再删除。因为没有删除数据，所以此操作不回收磁盘空间，
    因而具有速度比较快的优点。被标为 “未使用” 的列可在以后系统活动较少时从表中删除。 
    未使用的列就像不属于表一样。查询时看不到未使用列中的数据。此外，在执行 DESCRIBE 命令时，
    也不会显示这些列的名称和数据类型。用户可以添加与未使用的列同名的新列。 
    如果想删除同一表中的两列，则可先将列设置为 “未使用” 然后再删除。在删除两列时，
    表中的所有行都会更新两次；但如果将这些列设置为 “未使用” 然后再删除，则所有的行仅更新一次。
</code></pre>
<h2 id="索引管理"><a href="#索引管理" class="headerlink" title="索引管理"></a>索引管理</h2><pre class=" language-bash"><code class="language-bash">1.创建一个基于函数的索引（creating function-based indexes）
create index summit.item_quantity on summit.item<span class="token punctuation">(</span>quantity-quantity_shipped<span class="token punctuation">)</span><span class="token punctuation">;</span>

基于函数的索引（function-based indexes）:如果在表中要建立索引的一列或多列上使用了函数或表达式，则创建的是基于函数的索引。
基于函数的索引预先计算函数或表达式的值，病将结果存储在索引中。可以将基于函数的索引创建为B树或位图索引。

2.创建一个B树索引（create a B-tree index）
creae <span class="token punctuation">[</span> unique <span class="token punctuation">]</span> index index_name on table_name <span class="token punctuation">(</span>column,<span class="token punctuation">..</span> asc/desc<span class="token punctuation">)</span>
 tablespace tablespace_name <span class="token punctuation">[</span> pctfree integer <span class="token punctuation">]</span> 
<span class="token punctuation">[</span> initrans integer <span class="token punctuation">]</span>
<span class="token punctuation">[</span> maxtrans integer <span class="token punctuation">]</span>
<span class="token punctuation">[</span> logging <span class="token operator">|</span> nologging <span class="token punctuation">]</span>
<span class="token punctuation">[</span> nosort <span class="token punctuation">]</span>
storage<span class="token punctuation">(</span>initial 200k next 200k pctincrease 0 maxextents 50<span class="token punctuation">)</span><span class="token punctuation">;</span>

B树：平衡二叉树，oracle中用的最多的索引模式，使用与取值唯一性高的情况。只有两层，非叶级、叶级（指针所在级）

3.索引中pctfree参数计算公式
pctfree<span class="token punctuation">(</span>index<span class="token punctuation">)</span><span class="token operator">=</span><span class="token punctuation">(</span>maximum number of rows-initial number of rows<span class="token punctuation">)</span>*100/maximum number of rows

4.创建一个反向键索引（creating reverse key indexes）
create unique index xay_id on xay<span class="token punctuation">(</span>a<span class="token punctuation">)</span> reverse pctfree 30 storage <span class="token punctuation">(</span>initial 200k next 200k pctincrease 0 maxextents 50<span class="token punctuation">)</span> tablespace indx<span class="token punctuation">;</span>

5.创建位图索引（creating reverse key indexes）
create unique index say_id on xay<span class="token punctuation">(</span>a<span class="token punctuation">)</span> reverse pctfree 30 storage <span class="token punctuation">(</span>initial 200k next 200k pctincrease 0 maxextents 50<span class="token punctuation">)</span> tablespace indx<span class="token punctuation">;</span>

在下列情况中，位图索引比 B 树索引更有利： 
    ?        当表包含数百万行且键列的基数很低（即，该列中重复的值很多）时。例如，对于包含护照记录的表的性别列和婚姻状况列而言，位图索引比 B 树索引更适合 
    ?        当查询经常使用涉及 OR 运算符的多个 WHERE 条件组合时 
    ?        当键列上存在只读或很少的更新操作时 

    （位图索引适用于取值的唯一性很低的情况）

6.改变索引的存储参数（change storage parameter of index）
alter index xay_id storage <span class="token punctuation">(</span> next 400k maxextents 100 <span class="token punctuation">)</span>

7.为索引分配空间（allocating index space）
alter index xay_id allocate extent <span class="token punctuation">(</span>size 200k datafile <span class="token string">'c:/oracle/index.dbf'</span><span class="token punctuation">)</span>

8. alter index xay_id deallocate unused<span class="token punctuation">;</span>

手动分配索引空间： 
    在表上进行频繁的插入操作前，可能需要向索引添加区。添加区可防止索引动态扩展并导致性能降低。 

    （其中指定的数据文件一定是索引所在表空间的数据文件）
</code></pre>
<h2 id="约束管理"><a href="#约束管理" class="headerlink" title="约束管理"></a>约束管理</h2><pre class=" language-bash"><code class="language-bash">1.将约束定义为立即（immediate）或延迟（deferred）（define constraints as immediate or deferred）
alter session <span class="token keyword">set</span> constraint<span class="token punctuation">[</span>s<span class="token punctuation">]</span><span class="token operator">=</span>immediate/deferred/default<span class="token punctuation">;</span>
<span class="token keyword">set</span> constraint<span class="token punctuation">[</span>s<span class="token punctuation">]</span> constraint_name/all immediate/deferred<span class="token punctuation">;</span>

<span class="token keyword">set</span> constraints 语句用于将特定事务的约束设置为 deferred 或 immediate.可以使用此语句设置约束名称列表或约束的模式。
<span class="token keyword">set</span> constraints模式将一直持续到事务处理完成或者另一个set constraints语句重置模式
<span class="token keyword">set</span> constraints语句还包含将约束设置为immediate或deferred的子句set constraints.此命令缺省为设置所有（all） 可延迟的约束（不能指定约束名称列表）。
alter session <span class="token keyword">set</span> constraints 语句仅适用于当前会话

2.删除表或表空间时连带删除其上的外检（约束）
drop table table_name cascade constraints
or
drop tablespace tablespace_name including contents cascade constraints

在删除父表之前，必须先删除外键。可以使用以下一条语句同事执行这两个操作：
drop table table_name cascade constraints

在未删除或禁用外键之前无法（truncated）父表。

在删除包含附表的表空间之前，必须先删除外键。可使用下列命令完成该操作：
drop tablespace tablespace_name including contents
cascade constraints

如果从父表中删除行时没有使用 delete cascade 选项，oracle 服务器必须确保子表中的行不包含相应的外键。同样，仅当子行中不包含旧键值时，才允许更新父键。
如果字表的外键上没有索引，则oracle服务器索引子表并禁止更改以确保引用完整性。
如果表上有索引，则通过锁定索引项并避免子表上有更具限制性的锁来维护引用完整性。
如果必须从不同的事务处理同时更新两个表，则在外键列上创建索引。

3.在创建表时定义约束（define constraints <span class="token keyword">while</span> create a table ）
create table  xay<span class="token punctuation">(</span>id number<span class="token punctuation">(</span>7<span class="token punctuation">)</span> constraint xay_id pprimary key deferrable using index storage<span class="token punctuation">(</span>initial 100k next 100k <span class="token punctuation">)</span> tablespace indx<span class="token punctuation">)</span><span class="token punctuation">;</span>
primary key /
unique /
references table<span class="token punctuation">(</span>column<span class="token punctuation">)</span> ／
check

4.启用当前禁用的约束（enable constraints）
alter table xay <span class="token function">enable</span> novalidate constraint xay_id<span class="token punctuation">;</span>

启用novalidate:对于当前已有索引的 primary key 和 unique 约束，启用novalidate 约束比启用validate约束要快的多，这是应为，如果使用该选项启用约束，则不要求锁定表。
这种方法适合表上有许多DML活动的情况，如在oltp环境中。
    但是，如果需要创建索引，使用这中启用约束的方法并不能比enable validate带来更多的好处，因为oracle服务器在建立索引时锁定表。

5.启用约束（enable constraints）
alter table xay <span class="token function">enable</span> validate constraint xay_id<span class="token punctuation">;</span>
</code></pre>
<h2 id="约束管理-1"><a href="#约束管理-1" class="headerlink" title="约束管理"></a>约束管理</h2><pre class=" language-bash"><code class="language-bash">1.将约束定义为立即（immediate）或延迟（deferred）（define constraints as immediate or deferred）
alter session <span class="token keyword">set</span> constraint<span class="token punctuation">[</span>s<span class="token punctuation">]</span><span class="token operator">=</span>immediate/deferred/default<span class="token punctuation">;</span>
<span class="token keyword">set</span> constraint<span class="token punctuation">[</span>s<span class="token punctuation">]</span> constraint_name/all immediate/deferred<span class="token punctuation">;</span>

<span class="token keyword">set</span> constraints 语句用于将特定事务的约束设置为 deferred 或 immediate.可以使用此语句设置约束名称列表或约束的模式。
<span class="token keyword">set</span> constraints模式将一直持续到事务处理完成或者另一个set constraints语句重置模式
<span class="token keyword">set</span> constraints语句还包含将约束设置为immediate或deferred的子句set constraints.此命令缺省为设置所有（all） 可延迟的约束（不能指定约束名称列表）。
alter session <span class="token keyword">set</span> constraints 语句仅适用于当前会话

2.删除表或表空间时连带删除其上的外检（约束）
drop table table_name cascade constraints
or
drop tablespace tablespace_name including contents cascade constraints

在删除父表之前，必须先删除外键。可以使用以下一条语句同事执行这两个操作：
drop table table_name cascade constraints

在未删除或禁用外键之前无法（truncated）父表。

在删除包含附表的表空间之前，必须先删除外键。可使用下列命令完成该操作：
drop tablespace tablespace_name including contents
cascade constraints

如果从父表中删除行时没有使用 delete cascade 选项，oracle 服务器必须确保子表中的行不包含相应的外键。同样，仅当子行中不包含旧键值时，才允许更新父键。
如果字表的外键上没有索引，则oracle服务器索引子表并禁止更改以确保引用完整性。
如果表上有索引，则通过锁定索引项并避免子表上有更具限制性的锁来维护引用完整性。
如果必须从不同的事务处理同时更新两个表，则在外键列上创建索引。

3.在创建表时定义约束（define constraints <span class="token keyword">while</span> create a table ）
create table  xay<span class="token punctuation">(</span>id number<span class="token punctuation">(</span>7<span class="token punctuation">)</span> constraint xay_id pprimary key deferrable using index storage<span class="token punctuation">(</span>initial 100k next 100k <span class="token punctuation">)</span> tablespace indx<span class="token punctuation">)</span><span class="token punctuation">;</span>
primary key /
unique /
references table<span class="token punctuation">(</span>column<span class="token punctuation">)</span> ／
check

4.启用当前禁用的约束（enable constraints）
alter table xay <span class="token function">enable</span> novalidate constraint xay_id<span class="token punctuation">;</span>

启用novalidate:对于当前已有索引的 primary key 和 unique 约束，启用novalidate 约束比启用validate约束要快的多，这是应为，如果使用该选项启用约束，则不要求锁定表。
这种方法适合表上有许多DML活动的情况，如在oltp环境中。
    但是，如果需要创建索引，使用这中启用约束的方法并不能比enable validate带来更多的好处，因为oracle服务器在建立索引时锁定表。

5.启用约束（enable constraints）
alter table xay <span class="token function">enable</span> validate constraint xay_id<span class="token punctuation">;</span>
</code></pre>
<h2 id="加载（load）数据"><a href="#加载（load）数据" class="headerlink" title="加载（load）数据"></a>加载（load）数据</h2><pre class=" language-bash"><code class="language-bash">1.使用insert语句从另一张表中“直接加载”数据到新表（loading data using direct_load insert）
insert //*+append*/into emp nologging

<span class="token keyword">select</span> * from emp_old<span class="token punctuation">;</span>

2.使用sql*loader加载数据（using sql*loader）
sqlldr scott/tiger \
control <span class="token operator">=</span> ulcase6.ctl \
log <span class="token operator">=</span> ulcase6.log direct <span class="token operator">=</span> <span class="token boolean">true</span>
</code></pre>
<h2 id="重整数据"><a href="#重整数据" class="headerlink" title="重整数据"></a>重整数据</h2><pre class=" language-bash"><code class="language-bash">1.使用export导出数据（using export）
<span class="token variable">$exp</span> scott/tiger tables<span class="token punctuation">(</span>dept,emp<span class="token punctuation">)</span> file<span class="token operator">=</span>c:\emp.dmp log<span class="token operator">=</span>exp.log compress<span class="token operator">=</span>n direct<span class="token operator">=</span>y

2.使用import导入数据（using impport）
alter tablespace sales_ts <span class="token function">read</span> only<span class="token punctuation">;</span>
<span class="token variable">$expp</span> sys/<span class="token punctuation">..</span>  file<span class="token operator">=</span>xay.dmp transpport_tablespace<span class="token operator">=</span>y tablespace<span class="token operator">=</span>sales_ts triggers<span class="token operator">=</span>n constraints<span class="token operator">=</span>n
<span class="token variable">$copy</span> datafile
<span class="token variable">$imp</span> sys/<span class="token punctuation">..</span> file<span class="token operator">=</span>xay.dmp transpport_tablespppace<span class="token operator">=</span>y datafiles<span class="token operator">=</span><span class="token punctuation">(</span>/disk/sles01.dbf,/disk2/sles02.dbf<span class="token punctuation">)</span>

alter tablespace sales_ts <span class="token function">read</span> <span class="token function">write</span><span class="token punctuation">;</span>

4.checking transport <span class="token keyword">set</span>
DNMS_tts.transport_set_check<span class="token punctuation">(</span>ts_list <span class="token operator">=</span><span class="token operator">></span><span class="token string">'sales_ts'</span> <span class="token punctuation">..</span>,incl_constraints<span class="token operator">=</span><span class="token operator">></span>true<span class="token punctuation">)</span><span class="token punctuation">;</span>

在表transport_set_violations中查看
dbms_tts.isselfcontained 为true 是，表示自包含
</code></pre>
<h2 id="管理口令及相关资源"><a href="#管理口令及相关资源" class="headerlink" title="管理口令及相关资源"></a>管理口令及相关资源</h2><pre class=" language-bash"><code class="language-bash">1.修改用户账号、解锁及口令（controlling account lock and password）
alter user jumcky identified by oracle account unlock<span class="token punctuation">;</span>

2.user_privided password <span class="token keyword">function</span>
function_name<span class="token punctuation">(</span>userid <span class="token keyword">in</span> varchar2<span class="token punctuation">(</span>30<span class="token punctuation">)</span>,password <span class="token keyword">in</span> varchar2<span class="token punctuation">(</span>30<span class="token punctuation">)</span>,old_password <span class="token keyword">in</span> varchar2<span class="token punctuation">(</span>30<span class="token punctuation">))</span> <span class="token keyword">return</span> boolean

3.创建概要文件：设置口令参数（create a profile <span class="token keyword">:</span> password setting）
create profile grace_5 limit failed_login_attempts 3
password_lock_time unlimited password_life_time 30
password_reuse_time 30 password_verify_function verify_function
password_grace_time 5<span class="token punctuation">;</span>

4.修改概要文件（altering a profile）
alter profile default limit
failed_lofin_attemppts 3
password_life_time 60<span class="token punctuation">;</span>

5.删除概要文件（drop a profile）
drop profile grace_5 <span class="token punctuation">[</span>cascade<span class="token punctuation">]</span><span class="token punctuation">;</span>

6.创建概要文件：设置资源限制（create a profile <span class="token keyword">:</span> resource limit）
create profile developer_prof limit sessions_per_user 2
cpu_per_session 10000 idle_time 60 connect_time 480<span class="token punctuation">;</span>

7.view <span class="token operator">=</span><span class="token operator">></span>resource_cost <span class="token keyword">:</span> alter resource cost dba_users,dba_profiles

8.允许资源限制（enable resource limits）
alter system <span class="token keyword">set</span> resource_limit <span class="token operator">=</span> <span class="token boolean">true</span><span class="token punctuation">;</span>
</code></pre>
<h2 id="管理用户"><a href="#管理用户" class="headerlink" title="管理用户"></a>管理用户</h2><pre class=" language-bash"><code class="language-bash">1.创建用户（数据库认真方式）（create a user <span class="token keyword">:</span> database authentication）
create user juncky 
identified by oracle 
default tablespace <span class="token function">users</span> 
temporary tablespace 
temp <span class="token function">quota</span> 10m/unlimited 
on data password  expire <span class="token punctuation">[</span> account  lock <span class="token operator">|</span> unock <span class="token punctuation">]</span> <span class="token punctuation">[</span> profile profilename <span class="token operator">|</span> default <span class="token punctuation">]</span><span class="token punctuation">;</span>

2.修改用户的表空间限额（change user <span class="token function">quota</span> on tablespace）
alter user juncky <span class="token function">quota</span> 0 on <span class="token function">users</span><span class="token punctuation">;</span>

3.删除用户（drop  a user ）
drop user juncky <span class="token punctuation">[</span> cascade <span class="token punctuation">]</span><span class="token punctuation">;</span>

4.监控用户的视图（monitor user）
view：dba_users, dba_ts_quotas
</code></pre>
<h2 id="管理权限"><a href="#管理权限" class="headerlink" title="管理权限"></a>管理权限</h2><pre class=" language-bash"><code class="language-bash">1.系统权限（managing privileges）
view <span class="token operator">=</span><span class="token operator">></span>system_privilege_map ,dba_sys_privs,session_privs

2.授权系统权限（grant system privilege）
grant create session,create table to managers<span class="token punctuation">;</span>
or
grant create sessionto scott with admin option<span class="token punctuation">;</span>
with admin option can gran grant or revoke privilege from any user or role<span class="token punctuation">;</span>

3.sysdba和sysoper的权限（sysdba and sysoper privileges:）
sysoper:startup,shtdown,alter database <span class="token function">open</span> <span class="token operator">|</span> mount,alter database backup controlfile,alter tablespace
sysdba:sysoper privileges with admin option,create database,recover database <span class="token keyword">until</span>

4.口令文件成员视图（password <span class="token function">file</span> members:）
view:<span class="token operator">=</span>v<span class="token variable">$pwfile_users</span>

5.07_dictionary_accessibility <span class="token operator">=</span> <span class="token boolean">true</span>
restriction access to view or tables <span class="token keyword">in</span> other schema

6.撤销系统权限（revoke system privilege）
revoke create table from karen<span class="token punctuation">;</span>
or
revoke create session from scott<span class="token punctuation">;</span>

7.授权对象权限（grant object privilege）
grant execute on dbms_pipe to public<span class="token punctuation">;</span>
grant update<span class="token punctuation">(</span>first_name,salary<span class="token punctuation">)</span> on employee to karen with grant option<span class="token punctuation">;</span>

8.显示对象权限的视图（display object privilege）
view <span class="token operator">=</span><span class="token operator">></span>dba_tab_privs,dba_col_privs

9.撤销用户的对象权限（revoke object privilege）
revoke execute on dbms_pipe from scott <span class="token punctuation">[</span> cascade constraints <span class="token punctuation">]</span><span class="token punctuation">;</span>

10.审计记录视图（audit record view）
sys.aud$

11.保护审计线索（protecting the audit trail）
audit delete on sys.aud$ by access<span class="token punctuation">;</span>

保护审计线索：应保护审计线索，以防添加，修改或删除审计信息。
发布以下，命令：
audit delete on sys.aud$ by access<span class="token punctuation">;</span>

可防止审计线索未经授权即被删除<span class="token punctuation">;</span>只有DBA才拥有delete_catalog_role角色

12.语句审计（statement auditing）
audit user<span class="token punctuation">;</span>

13.权限审计（privilege auditing）
audit <span class="token keyword">select</span> any table by summit by access<span class="token punctuation">;</span>

权限审计：该种审计执行操作应具有的相应系统权限进行选择性审计，如audit create any trigger. 可以设置权限审计对数据库中的所选用户或每个用户进行审计。

14.方案对象审计（schema object auditing）
sudit lock on summit.employee by access whenever successful<span class="token punctuation">;</span>

方案对象审计：该种审计对待特定方案对象上的特定语句进行选择性审计，如 audit <span class="token keyword">select</span> on  hr.employees.方案对象审计始终适用于所有数据库用户。

15.审计选项视图（view audit option）
view <span class="token operator">=</span><span class="token operator">></span>all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,dba_obj_audit_opts

16.设计结果视图（view audit result）
view<span class="token operator">=</span><span class="token operator">></span>dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_session,dba_audit_statement
</code></pre>
<h2 id="管理角色"><a href="#管理角色" class="headerlink" title="管理角色"></a>管理角色</h2><pre class=" language-bash"><code class="language-bash">1.创建角色（create roles）
create role sales_clerk<span class="token punctuation">;</span>
or
create role hr_clerk identified by bonus<span class="token punctuation">;</span>
or
create role hr_manager identified externally<span class="token punctuation">;</span>

2.修改角色（modify role）
alter role sales_clerk identified by commission<span class="token punctuation">;</span>
or
alter role hr_clerk identified externally<span class="token punctuation">;</span>
or
alter role hr_manager not identified<span class="token punctuation">;</span>

3.分配角色（assigning roles）
grant sales_clerk to scott<span class="token punctuation">;</span>
oe
grant hr_clerk to hr_manager<span class="token punctuation">;</span>
or
grant hr_manager to scott with admin option<span class="token punctuation">;</span>

4.建立缺省角色（establish default role）
alter user scott default role hr_clerk,sales_clerk<span class="token punctuation">;</span>
or
alter user scott default role all<span class="token punctuation">;</span>
or
alter user scott default fole all except hr_clerk<span class="token punctuation">;</span>
or
alter user scott default role none<span class="token punctuation">;</span>

5.允许和禁止角色（enable and disable roles）
<span class="token keyword">set</span> role hr_clerk<span class="token punctuation">;</span>
or
<span class="token keyword">set</span> role sales_clerk identified by commission<span class="token punctuation">;</span>
or
<span class="token keyword">set</span> role all except sales_clerk<span class="token punctuation">;</span>
or
<span class="token keyword">set</span> role none<span class="token punctuation">;</span>

6.撤销用户的角色（remove role from user）
revoke sales_clerk from scott<span class="token punctuation">;</span>
or
revoke hr_manager from public <span class="token punctuation">;</span>

7.删除角色（remove role）
drop role hr_manager<span class="token punctuation">;</span>

8.显示角色信息的视图（display role information）
view:<span class="token operator">=</span><span class="token operator">></span>dba_roles.dba_role_privs,role_role_privs,dba_sys_privs,role_sys_privs,role_tab_privs,session_roles
</code></pre>
<h2 id="备份和恢复"><a href="#备份和恢复" class="headerlink" title="备份和恢复"></a>备份和恢复</h2><pre class=" language-bash"><code class="language-bash">1.备份恢复用相关视图
<span class="token function">v</span><span class="token variable">$sga</span>,v<span class="token variable">$instance</span>,v<span class="token variable">$process</span>,v<span class="token variable">$bgprocess</span>,v<span class="token variable">$database</span>,v<span class="token variable">$datafile</span>,v<span class="token variable">$sgastat</span>

2.为rman设置初始化参数
rman need <span class="token keyword">set</span> dbwr_io_slaves or backup_tape_io_slaves and large_pool_size

3.监控并行回滚的视图（monitoring parallel rollback）
<span class="token function">v</span><span class="token variable">$fast_start_servers</span>,v<span class="token variable">$fast_start_transactions</span>

4.执行一个冷备份（perform a closed database baskup <span class="token punctuation">(</span> noarchivelog <span class="token punctuation">)</span>）
<span class="token function">shutdown</span> immediate
<span class="token function">cp</span> files /backup/
startup

5.改变数据库文件的位置（restore to a different location）
connect system/manager as sysdba
startupp <span class="token function">mount</span>
alter database <span class="token function">rename</span> <span class="token function">file</span> <span class="token string">'/disk1/../user.dbf'</span> to <span class="token string">'/disk/../user.dbf'</span><span class="token punctuation">;</span>

6.恢复命令（recover）语法（recover syntax）
--recover a mounted database
recover database<span class="token punctuation">;</span>
recover datafile <span class="token string">'/disk1/data/df2.dbf'</span><span class="token punctuation">;</span>
alter database recover database<span class="token punctuation">;</span>
--recover an opened database
recover tablespace user_data<span class="token punctuation">;</span>
recover datafile 2<span class="token punctuation">;</span>
alter database recover datafile 2<span class="token punctuation">;</span>

7.设置自动应用重做日志文件（how to apply redo log files automatically）
<span class="token keyword">set</span> autorecovery on
recover automatic datafile 4<span class="token punctuation">;</span>

8.完全恢复（complete recovery）
--method 1 <span class="token punctuation">(</span> mounted databe <span class="token punctuation">)</span>
copy c:\backup\user.dbf  c:\oradata\user.dbf
startup <span class="token function">mount</span>
recover datafile <span class="token string">'c:\oradata\user.dbf'</span><span class="token punctuation">;</span>
alter database oppen<span class="token punctuation">;</span>

--mothod 2 <span class="token punctuation">(</span> opened database,initially opened, not system or rollback datafile<span class="token punctuation">)</span>
copy c:\backup\user.dbf  c:\oradata\user.dbf <span class="token punctuation">(</span> alter tablespace offline<span class="token punctuation">)</span>
recover datafile <span class="token string">'c:\oradata\user.dbf'</span> or
recover tablespace user_data<span class="token punctuation">;</span>
alter database datafile <span class="token string">'c:\oradata\user.dbf'</span> online or 
alter tablespace user_data online<span class="token punctuation">;</span>

--method 3 <span class="token punctuation">(</span> opened database,initially closed not system or rollback datafile <span class="token punctuation">)</span>
startup <span class="token function">mount</span>
alter database datafile <span class="token string">'c:\oradata\user.dbf'</span> offline<span class="token punctuation">;</span>
alter database <span class="token function">open</span>
copy c:\backup\user.dbf  d:\oradata\user.dbf
alter database <span class="token function">rename</span> <span class="token function">file</span> <span class="token string">'c:\oradata\user.dbf'</span> to <span class="token string">'d:\oradata\user.dbf'</span>
recover datafile <span class="token string">'e:\oradata\user.dbf'</span> or recover tablespace user_data<span class="token punctuation">;</span>
alter tablespace user_data online<span class="token punctuation">;</span>

--method 4 <span class="token punctuation">(</span> loss of data <span class="token function">file</span> with no backup and have all archive log <span class="token punctuation">)</span>
alter tablespace user_data offline immediate<span class="token punctuation">;</span>
alter database create datafile <span class="token string">'d:\oradata\user.dbf'</span> as  <span class="token string">'c:\oradata\user.dbf'</span>
recover tablespace user_data<span class="token punctuation">;</span>
alter tablespace user_data online

9.对一个打开的数据库进行备份（perform an <span class="token function">open</span> database backup）
alter tablespace user_data begin backup<span class="token punctuation">;</span>
copy files /backup/
alter database datafile <span class="token string">'/c:/../data.dbf'</span> end backup<span class="token punctuation">;</span>
alter system switch logfile<span class="token punctuation">;</span>

10.备份一个控制文件（backup a control file）
alter database backup controlfile to <span class="token string">'controll.bkp'</span><span class="token punctuation">;</span>
alter database backup xontrolfile to trace<span class="token punctuation">;</span>

11.非归档模式下的恢复（recovery （noarchivelog mode））
<span class="token function">shutdown</span> abort
<span class="token function">cp</span> files
startup

12.备份模式下的文件恢复（recovery of <span class="token function">file</span> <span class="token keyword">in</span> backup mode）
alter database datafile 2 end backup<span class="token punctuation">;</span>

13.清空重做日志文件（rclearing redo log file）
alter database <span class="token function">clear</span> unarchived logfile group 1<span class="token punctuation">;</span>
alter database <span class="token function">clear</span> unarchived logfile group 1 unrecoverable datafile<span class="token punctuation">;</span>

14.重做日志的恢复（删除与重建）（redo log recovery）
alter database add logfile group 3 <span class="token string">'c:\oradata\redo03.log'</span> size 1000k<span class="token punctuation">;</span>
alter database drop logfile group 1<span class="token punctuation">;</span>
alter database <span class="token function">open</span><span class="token punctuation">;</span>
or <span class="token operator">></span>cpp c:\oradata\redo02.log ,c:\oradata\redo01.log
alter database <span class="token function">clear</span> logfile ‘c:\oradata\log01.log’<span class="token punctuation">;</span>
</code></pre>

            </div>
            <hr>

            
            <style>
    #reward {
        margin: 40px 0;
        text-align: center;
    }

    #reward .reward-link {
        font-size: 1.88rem;
    }

    #reward .btn-floating:hover {
        box-shadow: 0 6px 12px rgba(0, 0, 0, 0.2), 0 5px 15px rgba(0, 0, 0, 0.2);
    }

    #rewardModal {
        width: 320px;
        height: 350px;
    }

    #rewardModal .reward-title {
        margin: 15px auto;
        padding-bottom: 5px;
    }

    #rewardModal .modal-content {
        padding: 10px;
    }

    #rewardModal .close {
        position: absolute;
        right: 15px;
        top: 15px;
        color: rgba(0, 0, 0, 0.5);
        font-size: 1.3rem;
        line-height: 20px;
        cursor: pointer;
    }

    #rewardModal .reward-tabs {
        margin: 0 auto;
        width: 210px;
    }

    .reward-tabs .tabs {
        height: 38px;
        margin: 10px auto;
        padding-left: 0;
    }

    .reward-tabs .tabs .tab {
        height: 38px;
        line-height: 38px;
    }

    .reward-tabs .tab a {
        color: #fff;
        background-color: #ccc;
    }

    .reward-tabs .tab a:hover {
        color: #fff;
    }

    .reward-tabs .wechat-tab .active {
        color: #fff;
        background-color: #22AB38;
    }

    .reward-tabs .alipay-tab .active {
        color: #fff;
        background-color: #019FE8;
    }

    .reward-tabs .reward-img {
        width: 210px;
        height: 210px;
    }
</style>

<div id="reward">
    <a class="reward-link btn-floating btn-large waves-effect waves-light red">赏</a>

    <!-- Modal Structure -->
    <div id="rewardModal" class="modal">
        <div class="modal-content">
            <a class="close"><i class="fa fa-close"></i></a>
            <h4 class="reward-title">你的赏识是我前进的动力</h4>
            <div class="reward-content">
                <div class="reward-tabs">
                    <ul class="tabs">
                        <li class="tab wechat-tab waves-effect waves-light"><a class="active" href="#wechat">微信</a></li>
                        <li class="tab alipay-tab waves-effect waves-light"><a href="#alipay">支付宝</a></li>
                    </ul>
                    <div id="wechat">
                        <img src="/medias/reward/wechat.png" class="reward-img" alt="微信打赏二维码">
                    </div>
                    <div id="alipay">
                        <img src="/medias/reward/alipay.jpg" class="reward-img" alt="支付宝打赏二维码">
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<script>
    $(function () {
        $('#reward .reward-link').on('click', function () {
            $('#rewardModal').openModal();
        });

        $('#rewardModal .close').on('click', function () {
            $('#rewardModal').closeModal();
        });
    });
</script>
            

            <link rel="stylesheet" type="text/css" href="/libs/share/css/share.min.css">

<div id="article-share">
    
    <div class="social-share" data-disabled="qzone" data-wechat-qrcode-helper="<p>微信里点“发现”->“扫一扫”二维码便可查看分享。</p>"></div>
    
</div>

<script src="/libs/share/js/social-share.min.js"></script>

            <div class="reprint">
                <p>
                    <span class="reprint-tip">转载请注明: </span>
                    <a href="https://zy20081.github.io" class="b-link-green">Zhou•Yong</a>
                    <i class="fa fa-angle-right fa-lg fa-fw text-color"></i>
                    <a href="/2019/01/02/orcale-zong-jie/" class="b-link-green">orcale总结</a>
                </p>
            </div>
        </div>
    </div>

    

    

    

    

    

    

<article id="prenext-posts" class="prev-next articles">
    <div class="row article-row">
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge left-badge text-color">上一篇</div>
            <div class="card">
                <a href="/2019/01/03/webclient-zhua-qu-wang-ye-dai-ma/">
                    <div class="card-image">
                        
                        
                        <img src="/medias/featureimages/20.jpg" class="responsive-img" alt="WebClient抓取网页代码">
                        
                        <span class="card-title">WebClient抓取网页代码</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary">WebClient类&emsp;WebClient类位于System.Net命名空间下，WebClient类提供向URI标识的任何本地、Intranet或Internet资源发送数据以及从这些资源接收数据的公共方法。源代码:
///引用命名</div>
                    <div class="publish-info">
                        <span class="publish-date">
                            <i class="fa fa-clock-o fa-fw icon-date"></i>2019-01-03
                        </span>
                        <span class="publish-author">
                            
                            <i class="fa fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/categories/ASP-NET/" class="post-category" target="_blank">
                                    ASP.NET
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/tags/ASP-NET/" target="_blank">
                        <span class="chip bg-color">ASP.NET</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge right-badge text-color">下一篇</div>
            <div class="card">
                <a href="/2019/01/02/http-xiang-ying-ma/">
                    <div class="card-image">
                        
                        
                        <img src="/medias/featureimages/22.jpg" class="responsive-img" alt="Http响应码">
                        
                        <span class="card-title">Http响应码</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary">1.Http请求&emsp;浏览器 www.baidu.com 先看本地电脑是否存在百度服务器的IP，如果存在直接 发送请求报文，如果不存在,则会先请求 DNS服务器，通过映射关系找到 百度 服务器的 IP地址返回给浏览器，浏览器保存IP </div>
                    <div class="publish-info">
                            <span class="publish-date">
                                <i class="fa fa-clock-o fa-fw icon-date"></i>2019-01-02
                            </span>
                        <span class="publish-author">
                            
                            <i class="fa fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/categories/前端/" class="post-category" target="_blank">
                                    前端
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/tags/Web/" target="_blank">
                        <span class="chip bg-color">Web</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
    </div>
</article>
</div>


    </div>
    <div class="col l3 hide-on-med-and-down">
        <div class="toc-widget">
            <div class="toc-title">目录</div>
            <div id="toc-content">

            </div>
        </div>
    </div>
</div>

<script src="/libs/tocbot/tocbot.min.js"></script>
<script>
    $(function () {
        tocbot.init({
            tocSelector: '#toc-content',
            contentSelector: '#articleContent',
            headingSelector: 'h2, h3, h4'
        });

        // modify the toc link href to support Chinese.
        let i = 0;
        let tocHeading = 'toc-heading-';
        $('#toc-content a').each(function () {
            $(this).attr('href', '#' + tocHeading + (++i));
        });

        // modify the heading title id to support Chinese.
        i = 0;
        $('#articleContent').children('h2, h3, h4').each(function () {
            $(this).attr('id', tocHeading + (++i));
        });

        // Set scroll toc fixed.
        let tocHeight = parseInt($(window).height() * 0.4 - 64);
        let $tocWidget = $('.toc-widget');
        $(window).scroll(function () {
            let scroll = $(window).scrollTop();
            /* add post toc fixed. */
            if (scroll > tocHeight) {
                $tocWidget.addClass('toc-fixed');
            } else {
                $tocWidget.removeClass('toc-fixed');
            }
        });
    });
</script>
    

</main>


<footer class="page-footer bg-color">
    <div class="container row center-align">
        <div class="col s12 m8 l8 copy-right">
            本站由ZhouYong 技术分享，心得分享.
        </div>
        <div class="col s12 m4 l4 social-link"><a href="#!" class="tooltipped" data-tooltip="邮件联系我: zy20081@126.com" data-position="top" data-delay="50">
    <i class="fa fa-envelope-open"></i>
</a>
<a href="#!" class="tooltipped" data-tooltip="QQ联系我: 314153271" data-position="top" data-delay="50">
    <i class="fa fa-qq"></i>
</a>

<a href="/atom.xml" class="tooltipped" target="_blank" data-tooltip="RSS 订阅" data-position="top" data-delay="50">
    <i class="fa fa-rss"></i>
</a>
</div>
    </div>
</footer>

<div class="progress-bar"></div>


<!-- 搜索遮罩框 -->
<div id="searchModal" class="modal">
    <div class="modal-content">
        <div class="search-header">
            <span class="title">搜索</span>
            <input type="search" id="searchInput" name="s" placeholder="请输入搜索的关键字" class="search-input" autofocus>
        </div>
        <div id="searchResult"></div>
    </div>
</div>

<script src="/js/search.js"></script>
<script type="text/javascript">
    searchFunc("/" + "search.xml", 'searchInput', 'searchResult');
</script>
<!-- 回到顶部按钮 -->
<div id="backTop" class="top-scroll">
    <a class="btn-floating btn-large waves-effect waves-light" href="#!">
        <i class="fa fa-angle-up"></i>
    </a>
</div>


<script src="/libs/materialize/js/materialize.min.js"></script>
<script src="/libs/masonry/masonry.pkgd.min.js"></script>
<script src="/libs/aos/aos.js"></script>
<script src="/libs/scrollprogress/scrollProgress.min.js"></script>
<script src="/libs/lightGallery/js/lightgallery-all.min.js"></script>

<script src="/libs/others/clicklove.js"></script>

<script src="/js/matery.js"></script>
<!-- Global site tag (gtag.js) - Google Analytics -->


</body>
</html>